﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace QuanLyThuVien.DAO
{
    class ChitietthanhlyDAO
    {
        //lay ra tat ca cac chi tiet thanh ly 
        public static DataGridView GetAllCTTL()
        {
            NorthwindDataContext context = new NorthwindDataContext();
            DataGridView dt = new DataGridView();
            var sql = from CTTL in context.ChiTietThanhLies
                      join S in context.Saches on CTTL.MaSach equals S.MaSach
                      join LDTL in context.LyDoThanhLies on CTTL.MaLyDoThanhLy equals LDTL.MaLyDoThanhLy
                      join TL in context.ThanhLies on CTTL.MaThanhLy equals TL.MaThanhLy
                      join NV in context.NhanViens on TL.MaNhanVien equals NV.MaNhanVien
         
                      select new 
                      {
                          S.MaSach,
                          S.TenSach,
                          LDTL.TenLyDoThanhLy,
                          NV.HoTenNhanVien,
                          TL.MaThanhLy,
                          LDTL.MaLyDoThanhLy,
                          CTTL.SoLuongThanhLy,
                          CTTL.MaChiTietThanhLy,
                          TL.NgayThanhLy,
                          
                      };

            dt.DataSource = sql;
            return dt;
        }


        public static DataGridView GetCTTLonMSach(int MaSach, string TenLyDoThanhLy, string TenThuKho)
        {
            NorthwindDataContext context = new NorthwindDataContext();
            DataGridView dt = new DataGridView();
            var sql = from CTTL in context.ChiTietThanhLies
                      join TL in context.ThanhLies on CTTL.MaThanhLy equals TL.MaThanhLy
                      join LDTL in context.LyDoThanhLies on CTTL.MaLyDoThanhLy equals LDTL.MaLyDoThanhLy
                      join S in context.Saches on CTTL.MaSach equals S.MaSach
                      join NV in context.NhanViens on TL.MaNhanVien equals NV.MaNhanVien
                      where S.MaSach == MaSach
                      where LDTL.TenLyDoThanhLy == TenLyDoThanhLy
                      where NV.HoTenNhanVien == TenThuKho
                      select new
                      {
                          S.MaSach,
                          S.TenSach,
                          LDTL.MaLyDoThanhLy,
                          LDTL.TenLyDoThanhLy,
                          TL.MaNhanVien,
                          TL.NgayThanhLy,
                          NV.HoTenNhanVien,
                          TL.MaThanhLy,

                      };

            dt.DataSource = sql;
            return dt;
        }

        public static DataGridView GetMaCTTL(int MaSach, int MaThanhLy, int MaLyDoThanhLy)
        {
            NorthwindDataContext context = new NorthwindDataContext();
            DataGridView dt = new DataGridView();
            var sql = from CTTL in context.ChiTietThanhLies
                      where CTTL.MaSach == MaSach
                      && CTTL.MaThanhLy == MaThanhLy
                      && CTTL.MaLyDoThanhLy == MaLyDoThanhLy
                      select CTTL;
            dt.DataSource = sql;
            return dt;
        }

        public static void Them(ChiTietThanhLy CTTL)
        {
            NorthwindDataContext context = new NorthwindDataContext();
            ChiTietThanhLy a = new ChiTietThanhLy();
            a.MaSach = CTTL.MaSach;
            a.MaLyDoThanhLy = CTTL.MaLyDoThanhLy;
            a.MaThanhLy = CTTL.MaThanhLy;
            a.SoLuongThanhLy = CTTL.SoLuongThanhLy;
            context.ChiTietThanhLies.InsertOnSubmit(a);
            context.SubmitChanges();
        }

        public static void Xoa(ChiTietThanhLy CTTL)
        {
            NorthwindDataContext context = new NorthwindDataContext();
            ChiTietThanhLy a = context.ChiTietThanhLies.Single(sCTTL => sCTTL.MaChiTietThanhLy == CTTL.MaChiTietThanhLy);
            context.ChiTietThanhLies.DeleteOnSubmit(a);
            context.SubmitChanges();

        }

        public static void Sua(ChiTietThanhLy CTTL)
        {

            NorthwindDataContext context = new NorthwindDataContext();
            ChiTietThanhLy a = context.ChiTietThanhLies.Single(scttl => scttl.MaChiTietThanhLy == CTTL.MaChiTietThanhLy);
            a.MaLyDoThanhLy = CTTL.MaLyDoThanhLy;
            a.MaSach = CTTL.MaSach;
            a.MaThanhLy = CTTL.MaThanhLy;
            a.SoLuongThanhLy = CTTL.SoLuongThanhLy;
            context.SubmitChanges();
        }
    }
}
